<?php
namespace app\common\service\finance;
use app\common\cache\finance\BillStatementsCache;
use app\common\model\agent\PromotionBillModel;
use app\common\model\finance\BillStatementsModel;
use app\common\model\member\MemberBillModel;
use app\common\model\Merchant\MerchantBillModel;
use app\common\model\order\ShopOrderModel;
use think\facade\Db;

/**
 * 财务报表
 */
class BillStatementsService
{
    /**
     * 添加、修改字段
     * @var array
     */
    public static $edit_field = [
            'id' => '',
        'is_disable' => '',
        'merchant_balance' => '',
        'member_balance' => '',
        'promotion_balance' => '',
        'recharge_amount' => '',
        'withdraw_amount' => '',
        'order_num' => '',
        'member_register_count' => '',
        'merchant_register_count' => '',
        'member_real_count' => '',
        'platform_balance' => '',
    ];
    /**
     * 财务报表列表
     *
     * @param array  $where 条件
     * @param int    $page  页数
     * @param int    $limit 数量
     * @param array  $order 排序
     * @param string $field 字段
     *
     * @return array
     */
    public static function list($where = [], $page = 1, $limit = 10,  $order = [], $field = '')
    {
        $model = new BillStatementsModel();
        $pk = $model->getPk();
        if (empty($field)) {
            $field = 'id,is_disable,create_time,update_time,merchant_balance,member_balance,promotion_balance,recharge_amount,withdraw_amount,order_num,member_register_count,merchant_register_count,member_real_count,platform_balance,date';
        }
        if (empty($order)) {
            $order = ['date'=>'desc',$pk => 'desc'];
        }
        if ($page == 0 || $limit == 0) {
            return $model->field($field)->where($where)->order($order)->select()->toArray();
        }
        $count = $model->where($where)->count();
        $pages = ceil($count / $limit);
        $list = $model->field($field)->where($where)->page($page)->limit($limit)->order($order)->select()->toArray();
        foreach ($list as $key=>$val){
            $order_num = $val['order_num']?json_decode($val['order_num'], true):[];
            foreach ($order_num as $k=>$v){
                $list[$key][$k] = $v;
            }
        }
        return compact('count', 'pages', 'page', 'limit', 'list');
    }

    /**
     * 创建全部数据
     * @Author: 易军辉
     * @DateTime:2024-11-12 16:47
     */
    public static function createAll()
    {
        $sql = <<<SQL
                SELECT date_list.date
                FROM (
                    SELECT date(commission_time) as date from ya_shop_order where commission_time is not null GROUP BY date(commission_time)
                    UNION
                    SELECT date(create_time) as date from ya_shop_order where create_time is not null GROUP BY date(create_time)
                    UNION
                    SELECT date(create_time) as date from ya_merchant_bill where create_time is not null GROUP BY date(create_time)
                    UNION
                    SELECT date(create_time) as date from ya_merchant where create_time is not null GROUP BY date(create_time)
                    UNION
                    SELECT date(create_time) as date from ya_member_bill where create_time is not null GROUP BY date(create_time)
                    UNION
                    SELECT date(create_time) as date from ya_member where create_time is not null GROUP BY date(create_time)
                    UNION
                    SELECT date(auth_time) as date from ya_member_id_card where auth_time is not null GROUP BY date(auth_time)
                    UNION
                    SELECT date(create_time) as date from ya_promotion_bill where create_time is not null GROUP BY date(create_time)
                ) AS date_list
                -- 左连接 ya_bill_statements 表，将没有匹配日期的记录过滤出来
                LEFT JOIN ya_bill_statements AS yb ON date_list.date = yb.date
                WHERE yb.date IS NULL
                ORDER BY date_list.date;
                SQL;
        $list = Db::query($sql);
        $count = 0;
        array_push($list,['date'=>date('Y-m-d')]);
        foreach ($list as $key=>$val){
            if(self::createData($val['date'])){
                $count +=1;
            }
        }
        return $count;
    }
    /**
     * 创建数据
     * @Author: 易军辉
     * @DateTime:2024-11-12 10:32
     * @param $date 指定日期
     */
    public static function createData($date=null)
    {
        if (!$date){
            $date = date('Y-m-d');
        }
        $start_time = date('Y-m-d 00:00:00',strtotime($date));
        $end_time = date('Y-m-d 23:59:59',strtotime($date));
        /***************************************已接单状态***************************************/
        $status_str = ShopOrderModel::getStatus('o_delivery',1).","
            .ShopOrderModel::getStatus('o_evaluate',1).","
            .ShopOrderModel::getStatus('o_auth',1).","
            .ShopOrderModel::getStatus('o_success',1);
        /***************************************充值、提现状态***************************************/
        $mer_recharge = MerchantBillModel::getType('RECHARGE',1);
        $mer_withdrawal = MerchantBillModel::getType('WITHDRAWAL',1);
        $member_recharge = MemberBillModel::getType('RECHARGE',1);
        $member_withdrawal = MemberBillModel::getType('WITHDRAWAL',1);
        $pro_withdrawal = PromotionBillModel::getTypes('withdrawal',1);
        /***************************************各平台订单数***************************************/
        $platform_num = Db::query('SELECT 
				store_platform_id,count(id) as num 
				from ya_shop_order 
				where 
				create_time BETWEEN "'.$start_time.'" AND "'.$end_time.'"
				and is_delete = 0
				GROUP BY store_platform_id');
        $platforms = Db::name('setting_store_platform')->where('is_disable',0)->where('is_delete',0)->field('id,code')->select();
        $platform_nums = [];
        foreach ($platforms as $k=>$v){
            $platform_nums[$v['code']] =0;
            foreach ($platform_num as $k1 => $v1) {
                if($v1['store_platform_id'] == $v['id']) {
                    $platform_nums[$v['code']] = $v1['num'];
                    break;
                }
            }
        }
        /***************************************统计数据查询***************************************/
        $sql = <<<SQL
                    SELECT
                        -- 平台收益
                        (SELECT SUM(mer_commission - commission)
                         FROM ya_shop_order
                         WHERE is_delete = 0
                           AND is_commission = 1
                           AND `status` IN ({$status_str})
                           AND commission_time BETWEEN "{$start_time}" AND "{$end_time}"
                        ) AS platform_revenue,
                        -- 商家余额
                        (SELECT SUM(bill.principal + bill.commission)
                         FROM ya_merchant AS a
                         LEFT JOIN ya_merchant_bill AS bill ON a.id = bill.mer_id
                         WHERE a.is_delete = 0
                           AND bill.create_time <= "{$end_time}"
                        ) AS merchant_balance,
                        -- 商家充值金额
                        (SELECT SUM(bill.principal + bill.commission)
                         FROM ya_merchant AS a
                         LEFT JOIN ya_merchant_bill AS bill ON a.id = bill.mer_id
                         WHERE a.is_delete = 0
                           AND bill.create_time BETWEEN "{$start_time}" AND "{$end_time}"
                           AND bill.type = {$mer_recharge}
                        ) AS merchant_recharge_amount,
                        -- 商家提现金额
                        (SELECT SUM(bill.principal + bill.commission)
                         FROM ya_merchant AS a
                         LEFT JOIN ya_merchant_bill AS bill ON a.id = bill.mer_id
                         WHERE a.is_delete = 0
                           AND bill.create_time BETWEEN "{$start_time}" AND "{$end_time}"
                           AND bill.type = {$mer_withdrawal}
                        ) AS merchant_withdraw_amount,
                        -- 商家注册数
                        (SELECT COUNT(id)
                         FROM ya_merchant
                         WHERE is_delete = 0
                           AND create_time BETWEEN "{$start_time}" AND "{$end_time}"
                        ) AS merchant_register_count,
                        -- 买手余额
                        (SELECT SUM(bill.principal + bill.commission)
                         FROM ya_member AS a
                         LEFT JOIN ya_member_bill AS bill ON a.member_id = bill.member_id
                         WHERE a.is_delete = 0
                           AND bill.create_time <= "{$end_time}"
                        ) AS member_balance,
                        -- 买手充值金额
                        (SELECT SUM(bill.principal + bill.commission)
                         FROM ya_member AS a
                         LEFT JOIN ya_member_bill AS bill ON a.member_id = bill.member_id
                         WHERE a.is_delete = 0
                           AND bill.create_time BETWEEN "{$start_time}" AND "{$end_time}"
                           AND bill.type = {$member_recharge}
                        ) AS member_recharge_amount,
                        -- 买手提现金额
                        (SELECT SUM(bill.principal + bill.commission)
                         FROM ya_member AS a
                         LEFT JOIN ya_member_bill AS bill ON a.member_id = bill.member_id
                         WHERE a.is_delete = 0
                           AND bill.create_time BETWEEN "{$start_time}" AND "{$end_time}"
                           AND bill.type = {$member_withdrawal}
                        ) AS member_withdraw_amount,
                        -- 买手注册数
                        (SELECT COUNT(member_id)
                         FROM ya_member
                         WHERE is_delete = 0
                           AND create_time BETWEEN "{$start_time}" AND "{$end_time}"
                        ) AS member_register_count,
                        -- 买手实名数
                        (SELECT COUNT(a.member_id)
                         FROM ya_member AS a
                         LEFT JOIN ya_member_id_card AS card ON a.member_id = card.member_id
                         WHERE a.is_delete = 0
                           AND card.is_delete = 0
                           AND card.is_disable = 0
                           AND card.auth_status = 2
                           AND card.auth_time BETWEEN "{$start_time}" AND "{$end_time}"
                        ) AS member_real_count,
                        -- 代理余额
                        (SELECT SUM(bill.money)
                         FROM ya_promotion_user AS a
                         LEFT JOIN ya_promotion_bill AS bill ON a.promotion_user_id = bill.promotion_user_id
                         WHERE a.is_delete = 0
                           AND bill.create_time <= "{$end_time}"
                        ) AS promotion_balance,
                        -- 代理提现金额
                        (SELECT SUM(bill.money)
                         FROM ya_promotion_user AS a
                         LEFT JOIN ya_promotion_bill AS bill ON a.promotion_user_id = bill.promotion_user_id
                         WHERE a.is_delete = 0
                           AND bill.type = {$pro_withdrawal}
                           AND bill.create_time BETWEEN "{$start_time}" AND "{$end_time}"
                        ) AS promotion_withdraw_amount
                SQL;
        $list = Db::query($sql);
        $data = [];
        foreach ($list as $key=>$val){
            $data = array(
                'merchant_balance'=>$val['merchant_balance']??0,//商家余额
                'member_balance'=>$val['member_balance']??0,//买手余额
                'promotion_balance'=>$val['promotion_balance']??0,//代理余额
                'recharge_amount'=>bcadd($val['merchant_recharge_amount']??0,$val['member_recharge_amount']??0,2),//充值金额
                'withdraw_amount'=>bcadd($val['merchant_withdraw_amount']??0,$val['member_withdraw_amount']??0,2),//提现金额
                'order_num'=>$platform_nums?json_encode($platform_nums, JSON_UNESCAPED_UNICODE):null,//订单数(各平台，json组装)
                'member_register_count'=>$val['member_register_count']??0,//买家注册数
                'merchant_register_count'=>$val['merchant_register_count']??0,//商家注册数
                'member_real_count'=>$val['member_real_count']??0,//实名用户数
                'platform_balance'=>$val['platform_revenue']??0,//平台收益
                'date'=>$date,//日期
            );
            //处理今日余额
            if($date == date('Y-m-d')){
                $data['merchant_balance'] = Db::name('merchant')->where('is_delete',0)->sum(Db::raw('mer_money + mer_commission'));
                $data['member_balance'] = Db::name('member')->where('is_delete',0)->sum(Db::raw('member_money + member_commission'));
                $data['promotion_balance'] = Db::name('promotion_user')->where('is_delete',0)->sum('balance');
            }
        }
        $model = new BillStatementsModel();
        $pk = $model->getPk();
        $statements_id = $model->where('date',$date)->where('is_disable',0)->where('is_delete',0)->value($pk);
        if($statements_id){
            $data['update_time'] = datetime();
            $res = $model->where($pk,$statements_id)->update($data);
        }else{
            $data['create_time'] = datetime();
            $res = $model->insert($data);
        }
        return $res;
    }
}
